﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using FrameWork.Lib;
using FrameWork.Lib.DataObjects;
using FrameWork.WinUI.Common;
using FrameWork.WinUI.Controls;

namespace REVISIONMAX30.Controls
{
    public partial class MySQLItemList : UserControl
    {
        public MySQLItemList()
        {
            InitializeComponent();
        }

        public UIManager UIManager { get; set; }
        public DataObjects.Group Group { get; set; }

        public void Init(DataObjects.Group pParentObject)
        {
            this.Group = pParentObject;
            this.UpdateViewer();
        }

        public List<Guid> SelectedGuidList
        {
            get
            {
                return this.lvSQLItems.SelectedItems.Cast<ListViewItem>().Select(t => t.Tag).Cast<Guid>().ToList();
            }
        }

        public List<DataObjects.SQLItem> SelectedSQLItemList
        {
            get
            {
                return this.Group.SQLItemList.Where(t => SelectedGuidList.Contains(t.DataObjectGUID)).ToList();
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            Group.SQLItemList.RemoveAll(t => this.SelectedGuidList.Contains(t.DataObjectGUID));

            this.UpdateViewer();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            this.Edit();
        }

        private void btnExecute_Click(object sender, EventArgs e)
        {

            //StringBuilder builder = new StringBuilder();
            //foreach (ListViewItem item in this.lvSQLItems.SelectedItems)
            //{
            //    SortedList<int, string> connectionStringIdValueList = Common.GetConnectionStringIdValueList(this._parent);
            //    try
            //    {
            //        SQLItem sQLItemById = Common.GetSQLItemById((int)item.Tag, this._parent);
            //        if (connectionStringIdValueList.ContainsKey(sQLItemById.connectionstringid))
            //        {
            //            string connectionString = connectionStringIdValueList[sQLItemById.connectionstringid];
            //            MySqlConnectionStringBuilder builder2 = new MySqlConnectionStringBuilder(connectionString);
            //            using (SQLiteConnection connection = Common.GetSQLiteConnection())
            //            {
            //                string str2;
            //                connection.Open();
            //                REVISIONMAX30.Log pErrorLog = new REVISIONMAX30.Log("", this._parent, connection);
            //                REVISIONMAX30.Log pOutLog = new REVISIONMAX30.Log("", this._parent, connection);
            //                if (sQLItemById.sourcetype == "sqlfile")
            //                {
            //                    str2 = Common.ExecuteBatch(builder2.UserID, builder2.Password, builder2.Database, sQLItemById.source, pErrorLog, pOutLog);
            //                    if (!str2.IsEmpty())
            //                    {
            //                        builder.AppendLine(str2);
            //                    }
            //                    if (builder.Length > 0)
            //                    {
            //                        break;
            //                    }
            //                    sQLItemById.executed = new DateTime?(DateTime.Now);
            //                    builder.Append(sQLItemById.SaveOrUpdate().ToString());
            //                    if (builder.Length > 0)
            //                    {
            //                        break;
            //                    }
            //                }
            //                else if (sQLItemById.sourcetype == "sourcetext")
            //                {
            //                    string tempFileName = Path.GetTempFileName();
            //                    File.WriteAllText(tempFileName, sQLItemById.source);
            //                    str2 = Common.ExecuteBatch(builder2.UserID, builder2.Password, builder2.Database, tempFileName, pErrorLog, pOutLog);
            //                    File.Delete(tempFileName);
            //                    if (!str2.IsEmpty())
            //                    {
            //                        builder.AppendLine(str2);
            //                    }
            //                    if (builder.Length > 0)
            //                    {
            //                        break;
            //                    }
            //                    sQLItemById.executed = new DateTime?(DateTime.Now);
            //                    builder.Append(sQLItemById.SaveOrUpdate().ToString());
            //                    if (builder.Length > 0)
            //                    {
            //                        break;
            //                    }
            //                }
            //                if (!pErrorLog.value.IsEmpty())
            //                {
            //                    pErrorLog.SaveOrUpdate();
            //                }
            //                if (!pOutLog.value.IsEmpty())
            //                {
            //                    pOutLog.SaveOrUpdate();
            //                }
            //            }
            //        }
            //    }
            //    catch (Exception exception)
            //    {
            //        builder.AppendLine(string.Format("Error: {0}", exception.Message));
            //    }
            //    if (builder.Length > 0)
            //    {
            //        MessageBox.Show(builder.ToString());
            //    }
            //}
            this.UpdateViewer();
        }

        private void btnExecuteViaADODB_Click(object sender, EventArgs e)
        {
            //StringBuilder builder = new StringBuilder();
            //foreach (ListViewItem item in this.lvSQLItems.SelectedItems)
            //{
            //    SortedList<int, string> connectionStringIdValueList = Common.GetConnectionStringIdValueList(this._parent);
            //    try
            //    {
            //        SQLItem sQLItemById = Common.GetSQLItemById((int)item.Tag, this._parent);
            //        if (connectionStringIdValueList.ContainsKey(sQLItemById.connectionstringid))
            //        {
            //            string connectionString = connectionStringIdValueList[sQLItemById.connectionstringid];
            //            MySqlConnectionStringBuilder builder2 = new MySqlConnectionStringBuilder(connectionString);
            //            using (SQLiteConnection connection = Common.GetSQLiteConnection())
            //            {
            //                string str2;
            //                connection.Open();
            //                REVISIONMAX30.Log pErrorLog = new REVISIONMAX30.Log("", this._parent, connection);
            //                REVISIONMAX30.Log pOutLog = new REVISIONMAX30.Log("", this._parent, connection);
            //                if (sQLItemById.sourcetype == "sqlfile")
            //                {
            //                    str2 = Common.ExecuteViaMYSQL(sQLItemById.source, builder2.ConnectionString, pErrorLog, pOutLog);
            //                    if (!str2.IsEmpty())
            //                    {
            //                        builder.AppendLine(str2);
            //                    }
            //                    if (builder.Length > 0)
            //                    {
            //                        break;
            //                    }
            //                    sQLItemById.executed = new DateTime?(DateTime.Now);
            //                    builder.Append(sQLItemById.SaveOrUpdate().ToString());
            //                    if (builder.Length > 0)
            //                    {
            //                        break;
            //                    }
            //                }
            //                else if (sQLItemById.sourcetype == "sourcetext")
            //                {
            //                    string tempFileName = Path.GetTempFileName();
            //                    File.WriteAllText(tempFileName, sQLItemById.source);
            //                    str2 = Common.ExecuteViaMYSQL(tempFileName, builder2.ConnectionString, pErrorLog, pOutLog);
            //                    File.Delete(tempFileName);
            //                    if (!str2.IsEmpty())
            //                    {
            //                        builder.AppendLine(str2);
            //                    }
            //                    if (builder.Length > 0)
            //                    {
            //                        break;
            //                    }
            //                    sQLItemById.executed = new DateTime?(DateTime.Now);
            //                    builder.Append(sQLItemById.SaveOrUpdate().ToString());
            //                    if (builder.Length > 0)
            //                    {
            //                        break;
            //                    }
            //                }
            //                if (!pErrorLog.value.IsEmpty())
            //                {
            //                    pErrorLog.SaveOrUpdate();
            //                }
            //                if (!pOutLog.value.IsEmpty())
            //                {
            //                    pOutLog.SaveOrUpdate();
            //                }
            //            }
            //        }
            //    }
            //    catch (Exception exception)
            //    {
            //        builder.AppendLine(string.Format("Error: {0}", exception.Message));
            //    }
            //    if (builder.Length > 0)
            //    {
            //        MessageBox.Show(builder.ToString());
            //    }
            //}
            //this.UpdateViewer();
        }

        private void btnExpire_Click(object sender, EventArgs e)
        {
            foreach (var tSQLItem in SelectedSQLItemList)
            {
                if (tSQLItem.Expired == null)
                    tSQLItem.Expired = DateTime.Now;
                else
                    tSQLItem.Expired = null;
            }

            this.UpdateViewer();
        }

        private void btnNew_Click(object sender, EventArgs e)
        {

            this.UIManager.ViewSQLItem(new DataObjects.SQLItem(), this.Group);

            this.UpdateViewer();
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            this.UpdateViewer();
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            foreach (var tSQLItem in SelectedSQLItemList)
            {
                tSQLItem.Executed = null;
            }

            this.UpdateViewer();
        }

        private void btnShowExpired_Click(object sender, EventArgs e)
        {
            this.UpdateViewer();
        }

        private void btnView_Click(object sender, EventArgs e)
        {
            foreach (var tSQLItem in SelectedSQLItemList)
            {
                if (tSQLItem.SourceType == "SQLFILE")
                {
                    System.Diagnostics.Process.Start(tSQLItem.Source);
                }

            }
        }

        private void Edit()
        {
            if (this.lvSQLItems.SelectedItems.Count > 0)
            {
                var tSelectedGUID = (Guid)this.lvSQLItems.SelectedItems[0].Tag;
                this.UIManager.ViewSQLItem(this.Group.SQLItemList.Where(t => t.DataObjectGUID == tSelectedGUID).First(), this.Group);
                this.UpdateViewer();
            }
        }

        private void lvSQLItems_DoubleClick(object sender, EventArgs e)
        {
            this.Edit();
        }

        public void UpdateViewer()
        {

            var tConnectionStrings = this.Group.ConnectionStringList.Select(t => new { Key = (Guid?)t.DataObjectGUID, Value = t.Name }).ToList();
            this.cboConnectionStrings.DataSource = tConnectionStrings;
            this.cboConnectionStrings.DisplayMember = "Value";
            this.cboConnectionStrings.ValueMember = "Key";

            this.lvSQLItems.Items.Clear();

            IEnumerable<DataObjects.SQLItem> sqlitems = null;

            if (!btnShowExpired.Checked)
                sqlitems = this.Group.SQLItemList.Where(j => j.Expired == null).OrderBy(t => t.Revision).ThenBy(t => t.ExecutionOrderIndex).ThenBy(t => t.SelectedConnectionStringName);
            else
                sqlitems = this.Group.SQLItemList.OrderBy(t => t.Revision).ThenBy(t => t.ExecutionOrderIndex).ThenBy(t => t.SelectedConnectionStringName);


            foreach (var sqlitem in sqlitems)
            {
                AddOrUpdateItem(sqlitem);
            }

            if (this.lvSQLItems.Items.Count > 0)
            {
                this.lvSQLItems.Columns[0].AutoResizeHeaderSize();
                this.lvSQLItems.Columns[1].AutoResize();
                this.lvSQLItems.Columns[2].AutoResize();
                this.lvSQLItems.Columns[3].AutoResizeHeaderSize();
                this.lvSQLItems.Columns[4].AutoResizeHeaderSize();
                this.lvSQLItems.Columns[5].AutoResize();
                this.lvSQLItems.Columns[6].AutoResize();
                this.lvSQLItems.Columns[7].AutoResize();
            }
        }

        int alternatingcolorrevision = 0;
        Color alternatingcolor = Color.WhiteSmoke;

        public void AddOrUpdateItem(DataObjects.SQLItem sqlitem)
        {
            if (this.InvokeRequired)
            {
                this.Invoke(new EventManager.DelegateAddOrUpdateItem<DataObjects.SQLItem>(AddOrUpdateItem), sqlitem);
            }
            else
            {
                var key = sqlitem.DataObjectGUID.ToString();
                ListViewItem item;

                if (lvSQLItems.Items.ContainsKey(key))
                {
                    item = this.lvSQLItems.Items[key];

                    item.SubItems[0].Text = sqlitem.ExecutionOrderIndex.ToString();
                    item.SubItems[1].Text = sqlitem.SourceName;
                    item.SubItems[2].Text = sqlitem.SourceType;
                    item.SubItems[3].Text = sqlitem.Revision.ToString();
                    item.SubItems[4].Text = sqlitem.SelectedConnectionStringName;
                    item.SubItems[5].Text = sqlitem.Created.StringValueOrEmpty("MM/dd/yyyy HH:mm");
                    item.SubItems[6].Text = sqlitem.Updated.StringValueOrEmpty("MM/dd/yyyy HH:mm");
                    item.SubItems[7].Text = sqlitem.Executed.StringValueOrEmpty("MM/dd/yyyy HH:mm");
                }
                else
                {
                    item = this.lvSQLItems.Items.Add(key, sqlitem.ExecutionOrderIndex.ToString(), -1);
                    item.Tag = sqlitem.DataObjectGUID;
                    item.SubItems.Add(sqlitem.SourceName);
                    item.SubItems.Add(sqlitem.SourceType);
                    item.SubItems.Add(sqlitem.Revision.ToString());
                    item.SubItems.Add(sqlitem.SelectedConnectionStringName);
                    item.SubItems.Add(sqlitem.Created.StringValueOrEmpty("MM/dd/yyyy HH:mm"));
                    item.SubItems.Add(sqlitem.Updated.StringValueOrEmpty("MM/dd/yyyy HH:mm"));
                    item.SubItems.Add(sqlitem.Executed.StringValueOrEmpty("MM/dd/yyyy HH:mm"));

                    if (alternatingcolorrevision != sqlitem.Revision)
                    {
                        if (alternatingcolor == Color.WhiteSmoke) alternatingcolor = Color.White;
                        else alternatingcolor = Color.WhiteSmoke;
                    }
                    item.BackColor = alternatingcolor;
                }

                if (sqlitem.Expired.HasValue)
                {
                    item.ForeColor = Color.Red;
                }
                else if (sqlitem.Executed.HasValue && !sqlitem.LastExecutionResult.HasErrorOrCritical())
                {
                    item.ForeColor = Color.DarkGreen;
                }
                else
                {
                    item.ForeColor = Color.DarkOrange;
                }
            }
        }

        private void btnSetExecutionOrderIndexAndREvision_Click(object sender, EventArgs e)
        {
            var tRevision = this.txtRevision.Text.IntValueOrZero();
            var tIndex = this.txtIndex.Text.IntValueOrZero();
            if ((tIndex == 0) || this.txtRevision.Text.IsEmpty())
            {
                MessageBox.Show("revision must be specified and index must be more then 0.");
            }

            else
            {
                foreach (var tSQLItem in SelectedSQLItemList)
                {
                    tSQLItem.Revision = tRevision;
                    tSQLItem.ExecutionOrderIndex = tIndex;
                }

                this.UpdateViewer();
            }
        }

        private void btnSetConnectionString_Click(object sender, EventArgs e)
        {
            var tSelectedValue = (Guid?)this.cboConnectionStrings.SelectedValue;
            var tSelectedText = (string)this.cboConnectionStrings.SelectedText;
            if (!tSelectedValue.HasValue())
            {
                MessageBox.Show("Please select Connection String to Set.");
            }
            else if (!this.Group.ConnectionStringList.Exists(t => t.DataObjectGUID == tSelectedValue.Value))
            {
                MessageBox.Show(string.Format("Connection String {0} is not valid.", tSelectedText));
            }
            else
            {
                var tConnectionString = this.Group.ConnectionStringList.Where(t => t.DataObjectGUID == tSelectedValue.Value).FirstOrDefault();

                foreach (var tSQLItem in SelectedSQLItemList)
                {
                    tSQLItem.SelectedConnectionStringGUID = tConnectionString.DataObjectGUID;
                }

                this.UpdateViewer();

            }
        }

        private void btnExecuteSelectedViaBatch_Click(object sender, EventArgs e)
        {
            this.Group.ExecuteSQLItems(this.UIManager.EventManager, new EventManager.DelegateAddOrUpdateItem<DataObjects.SQLItem>(AddOrUpdateItem), this.SelectedSQLItemList);
        }

        public delegate void DelegateEnable(bool enable);

        public void ExecutionEnabled(bool enabled)
        {
            if (this.InvokeRequired)
            {
                this.Invoke(new DelegateEnable(ExecutionEnabled), enabled);
            }
            else
            {
                btnExecuteSelectedViaBatch.Enabled = enabled;
                btnExecuteViaMySQLConnector.Enabled = enabled;
                btnReset.Enabled = enabled;
                btnExpire.Enabled = enabled;


            }
        }
    }
}
